package com.tpc.control.bean;

import javax.faces.event.ActionEvent;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;

import com.tpc.control.jpa.*; //import com.tpc.control.util.WriteExcel;

import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import jxl.write.WriteException;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Con110 {
	private String item_no;
	private String customer_no;
	private String division;
	private Date date_low;
	private Date date_high;
	private Date date_reserve_low, data_reserve_high;
//	private ReserveItemDAO reserveItemDAO = new ReserveItemDAO();
//	private ReserveHeaderDAO reserveHeaderDAO = new ReserveHeaderDAO();
	private List<Con110Report> report = new ArrayList<Con110Report>();
//	private List<ReserveItem> reserveitem = new ArrayList<ReserveItem>();
	private List<ReserveItemLab> itemLab = new ArrayList<ReserveItemLab>();
//	private ReserveHeader reserveheader = new ReserveHeader();
//	private ReserveHeader reserveheader2 = new ReserveHeader();
//	private ReserveItemLabDAO reserveItemLabDAO = new ReserveItemLabDAO();

	private Object maxDate;
	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private String inputFile;

	// private Status reserveStatus = new Status();
	// private StatusDAO statusDAO = new StatusDAO();
	// private StatusId id = new StatusId();
	private SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy",
			new Locale("en", "en"));
	private DecimalFormat af = new DecimalFormat("###,##0.00");

	public void showReport(ActionEvent e) {
		System.out.println("Con110:showReport");
		report.clear();
		if (customer_no.length() == 8) {
			customer_no = "00" + customer_no;
		} else if (customer_no.length() == 9) {
			customer_no = "0" + customer_no;
		}

		System.out.println("Start.............................................");

		ReserveItemDAO reserveItemDAO = new ReserveItemDAO();
		List<ReserveItem> reserveItems = reserveItemDAO.findReport(this.item_no, this.division,
				this.date_low, this.date_high, this.customer_no,
				this.date_reserve_low, this.data_reserve_high);

		int i;

		for (i = 0; i < reserveItems.size(); i++) {
			try {
				Con110Report rep = new Con110Report();
				ReserveItem aReserveItem = reserveItems.get(i);
				rep.setId(aReserveItem.getId().getReserveNo());
				rep.setDivision((aReserveItem.getDivision()));
				rep.setReserveHeader(aReserveItem.getId()
						.getReserveLine().toString());

				StatusId id = new StatusId();
				id.setStatusCode(aReserveItem.getStatus());
				id.setStatusType("RESERVE");
				StatusDAO statusDAO = new StatusDAO();
				Status reserveStatus = statusDAO.findById(id);
				rep.setStatus(reserveStatus.getStatusName());

				rep.setStatus(reserveStatus.getStatusName());
				rep.setMaterialNo(aReserveItem.getMaterialNo());
				rep.setMaterialDesign(aReserveItem.getMaterialDesign());
				rep
						.setReserveQty(af.format(aReserveItem
								.getReserveQty()));
				rep.setUom(aReserveItem.getUom());
				try {
					rep.setDeliveryDate(df.format(aReserveItem.getDeliveryDate()));
				} catch (Exception a1) {
					rep.setDeliveryDate("");
				}

				try {
					rep.setReceiveRawMaterialPlan(df.format(aReserveItem
							.getReceiveRawMaterialPlan()));
				} catch (Exception a4) {
					rep.setReceiveRawMaterialActual("");
				}
				try {
					rep.setReceiveRawMaterialActual(df.format(aReserveItem.getReceiveRawMaterialActual()));
				} catch (Exception a5) {
					rep.setReceiveRawMaterialActual("");
				}
				try {
					rep.setGreigeStartPlan(df.format(aReserveItem.getGreigeStartPlan()));
				} catch (Exception a6) {
					rep.setGreigeStartPlan("");
				}
				try {
					rep.setGreigeFinishPlan(df.format(aReserveItem.getGreigeFinishPlan()));
				} catch (Exception a7) {
					rep.setGreigeFinishPlan("");
				}
				try {
					rep.setDyeingStartPlan(df.format(aReserveItem.getDyeingStartPlan()));
				} catch (Exception a8) {
					rep.setDyeingStartPlan("");
				}
				try {
					rep.setDyeingFinishPlan(df.format(aReserveItem.getDyeingFinishPlan()));
				} catch (Exception a9) {
					rep.setDyeingFinishPlan("");
				}
				try{
					rep.setGreigeStartActual(df.format(aReserveItem.getGreigeStartActual()));
				}catch (Exception a10){
					a10.printStackTrace();
				}
				try{
					rep.setGreigeFinishActual(df.format(aReserveItem.getGreigeFinishActual()));
				}catch(Exception a11){
					a11.printStackTrace();
				}
				try{
					rep.setDyeingStartActual(df.format(aReserveItem.getDyeingStartActual()));
				}catch(Exception a12){
					a12.printStackTrace();
				}
				try{
					rep.setDyeingFinishActual(df.format(aReserveItem.getDyeingFinishActual()));
				}catch(Exception a13){
					a13.printStackTrace();
				}
				
				
				System.out.println("getReserveHeader: "+aReserveItem.getReserveHeader());
				ReserveHeader aReserveHeader;
				if (aReserveItem.getReserveHeader() == null) {
					ReserveHeaderDAO reserveHeaderDAO = new ReserveHeaderDAO();
					aReserveHeader = reserveHeaderDAO.findById(aReserveItem.getId().getReserveNo());
				} else {
					aReserveHeader = aReserveItem.getReserveHeader();
				}
				rep.setCustomer(aReserveHeader.getCustomer());
				rep.setCustomerName(aReserveHeader.getCustomerName());
				rep.setCreateDate(df.format(aReserveHeader.getCreateDate()));

				report.add(rep);

			} catch (Exception f) {
				f.printStackTrace();
			}

		}
	}

	public void showReportConfirmLab(ActionEvent y) {
		report.clear();
		if (customer_no.length() == 8) {
			customer_no = "00" + customer_no;
		} else if (item_no.length() == 9) {
			customer_no = "0" + customer_no;
		}
		System.out
				.println("Start.............................................");

		// reserveitem = reserveItemDAO.findReport(getItem_no().toString(),
		// getDivision().toString(), getDate_low().toString(),
		// getDate_high().toString());
		//		
		ReserveItemDAO reserveItemDAO = new ReserveItemDAO();
		List<ReserveItem> reserveitem = reserveItemDAO.findReport(this.item_no, this.division,
				this.date_low, this.date_high, this.customer_no,
				this.date_reserve_low, this.data_reserve_high);

		int i;
		for (i = 0; i < reserveitem.size(); i++) {
			try {
				try {
					ReserveItemLabDAO reserveItemLabDAO = new ReserveItemLabDAO();
					itemLab = reserveItemLabDAO.findByReserveNo(reserveitem
							.get(i).getId().getReserveNo(), reserveitem.get(i)
							.getId().getReserveLine());
				} catch (Exception d) {
					System.out.println("No itemlab");

				}
				if (itemLab.size() == 0) {
					Con110Report rep = new Con110Report();
					System.out.println("itemLab is null");
					rep.setId(reserveitem.get(i).getId().getReserveNo());
					rep.setDivision((reserveitem.get(i).getDivision()));
					rep.setReserveHeader(reserveitem.get(i).getId()
							.getReserveLine().toString());

					StatusId id = new StatusId();
					id.setStatusCode(reserveitem.get(i).getStatus());
					id.setStatusType("RESERVE");
					StatusDAO statusDAO = new StatusDAO();
					Status reserveStatus = statusDAO.findById(id);
					rep.setStatus(reserveStatus.getStatusName());

					rep.setMaterialNo(reserveitem.get(i).getMaterialNo());
					rep.setMaterialDesign(reserveitem.get(i)
							.getMaterialDesign());
					rep.setReserveQty(af.format(reserveitem.get(i)
							.getReserveQty()));
					rep.setUom(reserveitem.get(i).getUom());
					try {
						rep.setDeliveryDate(df.format(reserveitem.get(i)
								.getDeliveryDate()));
					} catch (Exception a1) {
						rep.setDeliveryDate("");
					}
					try {
						rep.setReceiveRawMaterialPlan(df.format(reserveitem
								.get(i).getReceiveRawMaterialPlan()));
					} catch (Exception a4) {
						rep.setReceiveRawMaterialActual("");
					}
					try {
						rep.setReceiveRawMaterialActual(df.format(reserveitem
								.get(i).getReceiveRawMaterialActual()));
					} catch (Exception a5) {
						rep.setReceiveRawMaterialActual("");
					}
					try {
						rep.setGreigeStartPlan(df.format(reserveitem.get(i)
								.getGreigeStartPlan()));
					} catch (Exception a6) {
						rep.setGreigeStartPlan("");
					}
					try {
						rep.setGreigeFinishPlan(df.format(reserveitem.get(i)
								.getGreigeFinishPlan()));
					} catch (Exception a7) {
						rep.setGreigeFinishPlan("");
					}
					try {
						rep.setDyeingStartPlan(df.format(reserveitem.get(i)
								.getDyeingStartPlan()));
					} catch (Exception a8) {
						rep.setDyeingStartPlan("");
					}
					try {
						rep.setDyeingFinishPlan(df.format(reserveitem.get(i)
								.getDyeingFinishPlan()));
					} catch (Exception a9) {
						rep.setDyeingFinishPlan("");
					}

					rep.setCustomer(reserveitem.get(i).getReserveHeader()
							.getCustomer());
					rep.setCustomerName(reserveitem.get(i).getReserveHeader()
							.getCustomerName());
					rep.setCreateDate(df.format(reserveitem.get(i)
							.getReserveHeader().getCreateDate()));

					report.add(rep);
				} else {
					for (int j = 0; j < itemLab.size(); j++) {
						Con110Report rep = new Con110Report();
						System.out.println(itemLab.size());

						rep.setReserveLabNo(itemLab.get(j).getId()
								.getReserveLabNo().toString());
						rep.setColorTpc(itemLab.get(j).getColorTpc());
						rep.setColorCustomer(itemLab.get(j).getColorCustomer());
						rep.setDetail(itemLab.get(j).getDetail());
						rep.setNoLab(itemLab.get(j).getNoLab());
						rep.setStandardDate(df.format(itemLab.get(j)
								.getStandardDate()));
						rep.setRemark(itemLab.get(j).getRemark());
						rep.setReserveLabDate(df.format(itemLab.get(j)
								.getReserveLabDate()));

						rep.setId(reserveitem.get(i).getId().getReserveNo());
						rep.setDivision((reserveitem.get(i).getDivision()));
						rep.setReserveHeader(reserveitem.get(i).getId()
								.getReserveLine().toString());

						StatusId id = new StatusId();
						id.setStatusCode(reserveitem.get(i).getStatus());
						id.setStatusType("RESERVE");
						StatusDAO statusDAO = new StatusDAO();
						Status reserveStatus = statusDAO.findById(id);
						rep.setStatus(reserveStatus.getStatusName());

						rep.setMaterialNo(reserveitem.get(i).getMaterialNo());
						rep.setMaterialDesign(reserveitem.get(i)
								.getMaterialDesign());
						rep.setReserveQty(af.format(reserveitem.get(i)
								.getReserveQty()));
						rep.setUom(reserveitem.get(i).getUom());
						try {
							rep.setDeliveryDate(df.format(reserveitem.get(i)
									.getDeliveryDate()));
						} catch (Exception a1) {
							rep.setDeliveryDate("");
						}

						try {
							rep.setReceiveRawMaterialPlan(df.format(reserveitem
									.get(i).getReceiveRawMaterialPlan()));
						} catch (Exception a4) {
							rep.setReceiveRawMaterialActual("");
						}
						try {
							rep.setReceiveRawMaterialActual(df
									.format(reserveitem.get(i)
											.getReceiveRawMaterialActual()));
						} catch (Exception a5) {
							rep.setReceiveRawMaterialActual("");
						}
						try {
							rep.setGreigeStartPlan(df.format(reserveitem.get(i)
									.getGreigeStartPlan()));
						} catch (Exception a6) {
							rep.setGreigeStartPlan("");
						}
						try {
							rep.setGreigeFinishPlan(df.format(reserveitem
									.get(i).getGreigeFinishPlan()));
						} catch (Exception a7) {
							rep.setGreigeFinishPlan("");
						}
						try {
							rep.setDyeingStartPlan(df.format(reserveitem.get(i)
									.getDyeingStartPlan()));
						} catch (Exception a8) {
							rep.setDyeingStartPlan("");
						}
						try {
							rep.setDyeingFinishPlan(df.format(reserveitem
									.get(i).getDyeingFinishPlan()));
						} catch (Exception a9) {
							rep.setDyeingFinishPlan("");
						}

						// rep.setCustomer(cusNameDate);
						rep.setCustomer(reserveitem.get(i).getReserveHeader()
								.getCustomer());
						rep.setCustomerName(reserveitem.get(i)
								.getReserveHeader().getCustomerName());
						rep.setCreateDate(df.format(reserveitem.get(i)
								.getReserveHeader().getCreateDate()));

						report.add(rep);
						// System.out.println(maxDate.toString());

					}
				}
			} catch (Exception f) {
				System.out.println(f);
				// ////////////////////////////////////////findall
			}
		}
	}

	public void back(ActionEvent z) {
		report.clear();
		this.item_no = "";
		this.division = "";
		this.date_high = null;
		this.date_low = null;
		this.date_reserve_low = null;
		this.data_reserve_high = null;
		/*
		 * customer_no; private String division; private Date date_low; private
		 * Date date_high; private Date date_reserve_low,data_reserve_high;
		 */
	}

	public void writeExcel(ActionEvent a) throws WriteException, IOException {
		setOutputFile();
		write();
	}

	public void setOutputFile() {
		this.inputFile = "c:/temp/excel.xls";
	}

	public void write() throws IOException, WriteException {

		javax.faces.context.FacesContext conText = javax.faces.context.FacesContext
				.getCurrentInstance();
		javax.servlet.http.HttpServletResponse response = (javax.servlet.http.HttpServletResponse) conText
				.getExternalContext().getResponse();

		/*
		 * HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 =
		 * wb.createSheet("Summary"); sheet1.createFreezePane(1, 11); CellStyle
		 * styleAmount = wb.createCellStyle(); DataFormat format =
		 * wb.createDataFormat();
		 * styleAmount.setDataFormat(format.getFormat("#,##0.00"));
		 */

		File file = new File(inputFile);
		WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));

		WritableWorkbook workbook = Workbook.createWorkbook(response
				.getOutputStream());
		workbook.createSheet("Report", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		createLabel(excelSheet);
		createContent(excelSheet);

		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition",
				"attachment; filename=con110_summary.xls");
		javax.servlet.ServletOutputStream out = response.getOutputStream();

		/* wb.write(out); */
		workbook.write();
		workbook.close();

		try {
			conText.getApplication().getStateManager().saveSerializedView(
					conText);
			conText.responseComplete();
			// Process p =
			// Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler c:/temp/excel.xls");
			// p.waitFor();

			System.out.println("Done");

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	private void createLabel(WritableSheet sheet) throws WriteException {
		// Lets create a times font
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(true);

		// Create create a bold font with unterlines
		WritableFont times10ptBoldUnderline = new WritableFont(
				WritableFont.TIMES, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);

		CellView cv = new CellView();
		cv.setFormat(times);
		cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);
		try {
			sheet.mergeCells(8, 0, 14, 0);
		} catch (Exception x) {
			System.out.println(x);
			System.out.println("merge fail");
		}
		// Write a few headers
		addCaption(sheet, 8, 0, "รายงานการจองข้อมูล Order");
		addCaption(sheet, 0, 1, "แผนก");
		addCaption(sheet, 1, 1, "เลขที่ใบจอง");
		addCaption(sheet, 2, 1, "ลำดับที่");
		addCaption(sheet, 3, 1, "รหัสลูกค้า");
		addCaption(sheet, 4, 1, "ชื่อลูกค้า");
		addCaption(sheet, 5, 1, "สถานะใบจอง");
		addCaption(sheet, 6, 1, "วันที่สร้างใบจอง");
		addCaption(sheet, 7, 1, "รหัส Com");
		addCaption(sheet, 8, 1, "รหัส Design");
		addCaption(sheet, 9, 1, "ปริมาณที่จอง");
		addCaption(sheet, 10, 1, "หน่วย");
		addCaption(sheet, 11, 1, "กำหนดส่ง");
		addCaption(sheet, 12, 1, "วันที่นัดวัตถุดิบเข้า");
		addCaption(sheet, 13, 1, "วันที่วัตถุดิบเข้าจริง");
		addCaption(sheet, 14, 1, "วันที่เริ่มผลิตเกรย์ตามแผน");
		addCaption(sheet, 15, 1, "วันที่ผลิตเกรย์เสร็จตามแผน");
		addCaption(sheet, 16, 1, "เริ่มผลิตเกรย์จริง");
		addCaption(sheet, 17, 1, "ผลิตเกรย์เสร็จจริง");
		addCaption(sheet, 18, 1, "วันที่เริ่มย้อม / ผลิตสินค้าตามแผน");
		addCaption(sheet, 19, 1, "วันที่ย้อม / ผลิตสินค้าเสร็จตามแผน");
		addCaption(sheet, 20, 1, "เริ่มผลิตจริง/เริ่มย้อมจริง");
		addCaption(sheet, 21, 1, "ผลิตเสร็จจริง/ย้อมเสร็จจริง");

	}

	private void createContent(WritableSheet sheet) throws WriteException,
			RowsExceededException {

		// data
		int j = 0;
		for (int i = 2; i < report.size() + 2; i++) {

			// First column
			addLabel(sheet, 0, i, report.get(j).getDivision());
			// Second column
			addLabel(sheet, 1, i, report.get(j).getId());
			addLabel(sheet, 2, i, report.get(j).getReserveHeader());
			addLabel(sheet, 3, i, report.get(j).getCustomer());
			addLabel(sheet, 4, i, report.get(j).getCustomerName());
			addLabel(sheet, 5, i, report.get(j).getStatus().toString());
			addLabel(sheet, 6, i, report.get(j).getCreateDate());
			addLabel(sheet, 7, i, report.get(j).getMaterialNo());
			addLabel(sheet, 8, i, report.get(j).getMaterialDesign());
			addLabel(sheet, 9, i, report.get(j).getReserveQty().toString());
			addLabel(sheet, 10, i, report.get(j).getUom());

			/*
			 * System.out.println(df.format(reserveitem.get(i).getDeliveryDate())
			 * ); System.out.println(i);
			 */

			addLabel(sheet, 11, i, report.get(j).getDeliveryDate());
			addLabel(sheet, 12, i, report.get(j).getReceiveRawMaterialPlan());
			addLabel(sheet, 13, i, report.get(j).getReceiveRawMaterialActual());
			addLabel(sheet, 14, i, report.get(j).getGreigeStartPlan());
			addLabel(sheet, 15, i, report.get(j).getGreigeFinishPlan());
			
			addLabel(sheet, 16, i, report.get(j).getGreigeStartActual());
			addLabel(sheet, 17, i, report.get(j).getGreigeFinishActual());
			
			addLabel(sheet, 18, i, report.get(j).getDyeingStartPlan());
			addLabel(sheet, 19, i, report.get(j).getDyeingFinishPlan());
			
			addLabel(sheet, 20, i, report.get(j).getDyeingStartActual());
			addLabel(sheet, 21, i, report.get(j).getDyeingFinishActual());
			j++;

		}
		System.out.println("Test");
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesBoldUnderline);
		sheet.addCell(label);
	}

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, times);
		sheet.addCell(number);
	}

	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}

	public void writeExcel2(ActionEvent excel) throws WriteException,
			IOException {
		//setOutputFile2();
		write2();
	}

	public void setOutputFile2() {
		this.inputFile = "c:/temp/excel.xls";
	}

	public void write2() throws IOException, WriteException {

		javax.faces.context.FacesContext conText2 = javax.faces.context.FacesContext
				.getCurrentInstance();
		javax.servlet.http.HttpServletResponse response2 = (javax.servlet.http.HttpServletResponse) conText2
				.getExternalContext().getResponse();

		File file = new File(inputFile);
		WorkbookSettings wbSettings2 = new WorkbookSettings();

		wbSettings2.setLocale(new Locale("en", "EN"));

		WritableWorkbook workbook2 = Workbook.createWorkbook(response2
				.getOutputStream());
		workbook2.createSheet("Report", 0);
		WritableSheet excelSheet2 = workbook2.getSheet(0);
		createLabel2(excelSheet2);
		createContent2(excelSheet2);

		response2.setContentType("application/vnd.ms-excel");
		response2.setHeader("Content-Disposition",
				"attachment; filename=con110_summary.xls");
		javax.servlet.ServletOutputStream out2 = response2.getOutputStream();

		/* wb.write(out); */
		workbook2.write();
		workbook2.close();

		try {
			conText2.getApplication().getStateManager().saveSerializedView(
					conText2);
			conText2.responseComplete();
			// Process p =
			// Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler c:/temp/excel.xls");
			// p.waitFor();

			System.out.println("Done");

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	private void createLabel2(WritableSheet sheet) throws WriteException {
		// Lets create a times font
		WritableFont times10pt2 = new WritableFont(WritableFont.TIMES, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt2);
		// Lets automatically wrap the cells
		times.setWrap(true);

		// Create create a bold font with unterlines
		WritableFont times10ptBoldUnderline2 = new WritableFont(
				WritableFont.TIMES, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline2);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);

		CellView cv2 = new CellView();
		cv2.setFormat(times);
		cv2.setFormat(timesBoldUnderline);
		cv2.setAutosize(true);
		try {
			sheet.mergeCells(8, 0, 14, 0);
		} catch (Exception x) {
			System.out.println(x);
			System.out.println("merge fail");
		}
		// Write a few headers
	
		addCaption(sheet, 8, 0, "รายงานการจองข้อมูล Order");
		addCaption(sheet, 0, 1, "แผนก");
		addCaption(sheet, 1, 1, "เลขที่ใบจอง");
		addCaption(sheet, 2, 1, "ลำดับที่");
		addCaption(sheet, 3, 1, "รหัสลูกค้า");
		addCaption(sheet, 4, 1, "ชื่อลูกค้า");
		addCaption(sheet, 5, 1, "สถานะใบจอง");
		addCaption(sheet, 6, 1, "วันที่สร้างใบจอง");
		addCaption(sheet, 7, 1, "รหัส Com");
		addCaption(sheet, 8, 1, "รหัส Design");
		addCaption(sheet, 9, 1, "ปริมาณที่จอง");
		addCaption(sheet, 10, 1, "หน่วย");
		addCaption(sheet, 11, 1, "กำหนดส่ง");
		addCaption(sheet, 12, 1, "วันที่นัดวัตถุดิบเข้า");
		addCaption(sheet, 13, 1, "วันที่วัตถุดิบเข้าจริง");
		addCaption(sheet, 14, 1, "วันที่เริ่มผลิตเกรย์ตามแผน");
		addCaption(sheet, 15, 1, "วันที่ผลิตเกรย์เสร็จตามแผน");
		addCaption(sheet, 16, 1, "เริ่มผลิตเกรย์จริง");
		addCaption(sheet, 17, 1, "ผลิตเกรย์เสร็จจริง");
		addCaption(sheet, 18, 1, "วันที่เริ่มย้อม / ผลิตสินค้าตามแผน");
		addCaption(sheet, 19, 1, "วันที่ย้อม / ผลิตสินค้าเสร็จตามแผน");
		addCaption(sheet, 20, 1, "เริ่มผลิตจริง/เริ่มย้อมจริง");
		addCaption(sheet, 21, 1, "ผลิตเสร็จจริง/ย้อมเสร็จจริง");
		addCaption2(sheet, 18, 1, "ลำดับที่ Confirm Lab");
		addCaption2(sheet, 19, 1, "ชื่อสี TPC");
		addCaption2(sheet, 20, 1, "ชื่อสีลูกค้า");
		addCaption2(sheet, 21, 1, "รายการ");
		addCaption2(sheet, 22, 1, "No.Lab");
		addCaption2(sheet, 23, 1, "วันที่นัด Confirm Lab");
		addCaption2(sheet, 24, 1, "วันที่ Confirm Lab จริง");
		addCaption2(sheet, 25, 1, "วันที่รับ Standard");
		addCaption2(sheet, 26, 1, "หมายเหตุ");
		addCaption2(sheet, 27, 1, "วันที่ตอบ Lab");
	}

	private void createContent2(WritableSheet sheet2) throws WriteException,
			RowsExceededException {

		// data
		int j = 0;
		for (int i = 2; i < report.size() + 2; i++) {

			// First column
			addLabel(sheet2, 0, i, report.get(j).getDivision());
			// Second column
			addLabel(sheet2, 1, i, report.get(j).getId());
			addLabel(sheet2, 2, i, report.get(j).getReserveHeader());
			addLabel(sheet2, 3, i, report.get(j).getCustomer());
			addLabel(sheet2, 4, i, report.get(j).getCustomerName());
			addLabel(sheet2, 5, i, report.get(j).getStatus().toString());
			addLabel(sheet2, 6, i, report.get(j).getCreateDate());
			addLabel(sheet2, 7, i, report.get(j).getMaterialNo());
			addLabel(sheet2, 8, i, report.get(j).getMaterialDesign());
			addLabel(sheet2, 9, i, report.get(j).getReserveQty().toString());
			addLabel(sheet2, 10, i, report.get(j).getUom());

			/*
			 * System.out.println(df.format(reserveitem.get(i).getDeliveryDate())
			 * ); System.out.println(i);
			 */

			addLabel(sheet2, 11, i, report.get(j).getDeliveryDate());
			addLabel(sheet2, 12, i, report.get(j).getReceiveRawMaterialPlan());
			addLabel(sheet2, 13, i, report.get(j).getReceiveRawMaterialActual());
			addLabel(sheet2, 14, i, report.get(j).getGreigeStartPlan());
			addLabel(sheet2, 15, i, report.get(j).getGreigeFinishPlan());
			
			addLabel(sheet2, 16, i, report.get(j).getGreigeStartActual());
			addLabel(sheet2, 17, i, report.get(j).getGreigeFinishActual());
			
			addLabel(sheet2, 18, i, report.get(j).getDyeingStartPlan());
			addLabel(sheet2, 19, i, report.get(j).getDyeingFinishPlan());
			
			addLabel(sheet2, 20, i, report.get(j).getDyeingStartActual());
			addLabel(sheet2, 21, i, report.get(j).getDyeingFinishActual());

			addLabel2(sheet2, 18, i, report.get(j).getReserveLabNo());
			addLabel2(sheet2, 19, i, report.get(j).getColorTpc());
			addLabel2(sheet2, 20, i, report.get(j).getColorCustomer());
			addLabel2(sheet2, 21, i, report.get(j).getDetail());
			addLabel2(sheet2, 22, i, report.get(j).getNoLab());
			addLabel2(sheet2, 23, i, report.get(j).getConfirmLabPlan());
			addLabel2(sheet2, 24, i, report.get(j).getConfirmLabActual());
			addLabel2(sheet2, 25, i, report.get(j).getStandardDate());
			addLabel2(sheet2, 26, i, report.get(j).getRemark());
			addLabel2(sheet2, 27, i, report.get(j).getReserveLabDate());

			j++;

		}
		System.out.println("Test");
	}

	private void addCaption2(WritableSheet sheet2, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesBoldUnderline);
		sheet2.addCell(label);
	}

	private void addNumber2(WritableSheet sheet2, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, times);
		sheet2.addCell(number);
	}

	private void addLabel2(WritableSheet sheet2, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s, times);
		sheet2.addCell(label);
	}

	public String go111() {
		return "con111";
	}

	public String getItem_no() {
		return item_no;
	}

	public void setItem_no(String item_no) {
		this.item_no = item_no;
	}

	public String getCustomer_no() {
		return customer_no;
	}

	public void setCustomer_no(String customer_no) {
		this.customer_no = customer_no;
	}

	public String getDivision() {
		return division;
	}

	public void setDivision(String division) {
		this.division = division;
	}

	public Date getDate_low() {
		return date_low;
	}

	public void setDate_low(Date date_low) {
		this.date_low = date_low;
	}

	public Date getDate_high() {
		return date_high;
	}

	public void setDate_high(Date date_high) {
		this.date_high = date_high;
	}

	public Date getDate_reserve_low() {
		return date_reserve_low;
	}

	public void setDate_reserve_low(Date date_reserve_low) {
		this.date_reserve_low = date_reserve_low;
	}

	public Date getData_reserve_high() {
		return data_reserve_high;
	}

	public void setData_reserve_high(Date data_reserve_high) {
		this.data_reserve_high = data_reserve_high;
	}

	public Object getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(Object maxDate) {
		this.maxDate = maxDate;
	}

	public List<Con110Report> getReport() {
		return report;
	}

	public void setReport(List<Con110Report> report) {
		this.report = report;
	}

}
